#!/use/bin/python
# coding=utf-8
# 数据分析-助眠功能-服务完成情况
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数

    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql,conf=2):
    if conf == 1:
        conn, cur = getConfConnection()
    else:
        conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num


#根据月份 获取本月指定指标的 总和
#tableName 指定的报表
#columnName 指定的指标字段
#date 月份
def getSumNumByTableColumnNameAndDate(tableName,columnName,date):
    conn, cur = getDataConnection()
    selectNumSql = f"""
                     select sum({columnName}) from {tableName} t where 1=1   
                     """;
    if len(date) >0:
        selectNumSql = f"""{selectNumSql} and SUBSTR( t.ref_date, 1,7) = '{date}' and LENGTH(t.ref_date) = 10  """
    cur.execute(selectNumSql)
    #print(selectNumSql);
    numResult = cur.fetchone()
    num = 0
    if numResult!= None:
        num = numResult[0]
    if num is None:
        return 0
    return num

#计算百分比 保留两位小数  如:34.88%
# X为分子 Y为分母
def getRateByXY(X,Y):
    rate = 0
    if(X is None or Y is None):
        return rate
    if Y != 0:
        rate = round(X / Y, 2)
    return rate

#数据分析-助眠功能-服务完成情况
def insertCompleteRate(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zmgn_complete_rate 表是否已经存在当天 数据
        countNum = selectNumBySql(f"select count(1) num from analysis_zmgn_complete_rate t where 1=1  and t.ref_date =  '{date}'")
        # 获取当日 执行助眠功能次数
        serviceNum = selectNumBySql(
            f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'saEnterPage' ")
        # 获取当日 执行助眠功能设备数
        serviceDeviceNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'saEnterPage' ")
        # 获取全部 执行助眠功能次数
        serviceAllNum = selectNumBySql(
            f"select ifnull(sum(service_num),0) from analysis_zmgn_complete_rate t where 1=1 and LENGTH(t.ref_date) = 10 and  t.ref_date <= '{date}' ")
        # 进入设置页次数
        enterSettingNum = selectNumBySql(
            f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'saShowConfigPage'  ")
        # 点击上下键切换白噪音的次数
        totalUpDownNum = selectNumBySql(
            f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'saWhiteNoiseChanged' ")
        avgUpDownNum = getRateByXY(totalUpDownNum, serviceNum)
        # 点击左右键切换时长的次数
        leftRightNum = selectNumBySql(
            f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'saDurationChanged'  and JSON_EXTRACT(msg,'$.choose') =2")
        # 总助眠时长
        totalSleepDuration = selectNumBySql(
            f""" select ifnull(sum(JSON_EXTRACT(msg,'$.duration'))/1000/60,0) from log_common_service_{yyMMddDate} t where t.`key` = 'saFinish'
             and  JSON_EXTRACT(msg,'$.duration') <= 6000000 """)
        avgSleepDuration = getRateByXY(totalSleepDuration, serviceNum)
        # 自动退出助眠服务次数
        autoExitNum = selectNumBySql(
            f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'saFinish'  and JSON_EXTRACT(msg,'$.isAuto') =1")
        # 挽留弹窗次数
        retentionPopupNum = selectNumBySql(
            f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'saRetention' ")
        # 手动退出
        manualExitNum = selectNumBySql(
            f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'saRetentionOperate'  and JSON_EXTRACT(msg,'$.result') =1")
        # 继续使用次数
        keepUsingNum = selectNumBySql(
            f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'saRetentionOperate'  and JSON_EXTRACT(msg,'$.result') !=1")
        # 服务异常次数
        errorNum = selectNumBySql(
            f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'saDownloadResult'  and JSON_EXTRACT(msg,'$.result') =0")

        if countNum == 0:
            logDetail = f"""
                        {date}日新增-助眠功能服务次数为{serviceNum},服务设备次数为{serviceDeviceNum},累计服务次数{serviceAllNum},进入设置页次数为{enterSettingNum},
                        点击上下键切换白噪音的次数为{totalUpDownNum},人均切换白噪音的次数{avgUpDownNum},点击左右键切换时长的次数{leftRightNum},总助眠时长{totalSleepDuration},
                        人均助眠时长{avgSleepDuration},自动退出助眠服务次数{autoExitNum},
                        挽留弹窗次数{retentionPopupNum},手动退出次数为{manualExitNum},继续使用次数为{keepUsingNum},服务异常次数为{errorNum}
                        """
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = f"""insert into analysis_zmgn_complete_rate(service_num,service_device_num,service_cumulate_num,enter_setting_num,total_up_down_num,avg_up_down_num,left_right_num,total_sleep_duration,avg_sleep_duration,
            auto_exit_num,retention_popup_num,manual_exit_num,keep_using_num,error_num,ref_date) 
                value({serviceNum},{serviceDeviceNum},{serviceAllNum},{enterSettingNum},{totalUpDownNum},{avgUpDownNum},{leftRightNum},{totalSleepDuration},{avgSleepDuration},
                {autoExitNum},{retentionPopupNum},{manualExitNum},{keepUsingNum},{errorNum},'{date}')"""
            #print(insertSql)
            cur.execute(insertSql)
        else:
            logDetail = f"""
                        {date}日更新-助眠功能服务次数为{serviceNum},服务设备次数为{serviceDeviceNum},累计服务次数{serviceAllNum},进入设置页次数为{enterSettingNum},
                        点击上下键切换白噪音的次数为{totalUpDownNum},人均切换白噪音的次数{avgUpDownNum},点击左右键切换时长的次数{leftRightNum},总助眠时长{totalSleepDuration},
                        人均助眠时长{avgSleepDuration},自动退出助眠服务次数{autoExitNum},
                        挽留弹窗次数{retentionPopupNum},手动退出次数为{manualExitNum},继续使用次数为{keepUsingNum},服务异常次数为{errorNum}
                        """
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = f"""
                update analysis_zmgn_complete_rate set service_num = {serviceNum},service_device_num= {serviceDeviceNum},service_cumulate_num= {serviceAllNum},enter_setting_num= {enterSettingNum}
                ,total_up_down_num= {totalUpDownNum},avg_up_down_num = {avgUpDownNum},left_right_num= {leftRightNum},total_sleep_duration= {totalSleepDuration},avg_sleep_duration= {avgSleepDuration},
                auto_exit_num= {autoExitNum},retention_popup_num= {retentionPopupNum},manual_exit_num= {manualExitNum},keep_using_num= {keepUsingNum},error_num= {errorNum} , update_time = now()  where
                    ref_date = '{date}' 
            """
            print(updateSql)
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 数据分析-助眠功能-服务完成情况  --  月度
def insertCompleteRateMonth(toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zmgn_complete_rate 表是否已经存在当天 数据
        countNum = selectNumBySql(f"select count(1) num from analysis_zmgn_complete_rate t where 1=1  and t.ref_date =  '{toMonthDay}'")
        # 取 本月 新增的 执行过助眠服务的次数
        serviceNum  = getSumNumByTableColumnNameAndDate('analysis_zmgn_complete_rate', 'service_num', toMonthDay)
        # 获取所有执行过助眠服务的次数
        serviceAllNum = selectNumBySql(
            f"select sum(service_num) from analysis_zmgn_complete_rate t where 1=1 and LENGTH(t.ref_date) = 10  ")
        # 获取 本月 新增的 执行过助眠服务的设备量
        serviceDeviceNum = getSumNumByTableColumnNameAndDate('analysis_zmgn_complete_rate', 'service_device_num', toMonthDay)

        if countNum == 0:
            logDetail = f"{toMonthDay}月新增-助眠功能服务次数为{serviceNum},服务设备次数为{serviceDeviceNum},累计服务次数{serviceAllNum}"
            #print(logDetail)
            # 插入早间服务可见设备量
            insertSql = f"""insert into analysis_zmgn_complete_rate(service_num,service_device_num,service_cumulate_num,ref_date) 
                value({serviceNum},{serviceDeviceNum},{serviceAllNum},'{toMonthDay}')"""
            cur.execute(insertSql)
        else:
            logDetail = f"{toMonthDay}月更新-助眠功能服务次数为{serviceNum},服务设备次数为{serviceDeviceNum},累计服务次数{serviceAllNum}"
            #print(logDetail)
            # 更新早间服务可见设备量
            updateSql = f"""
                update analysis_zmgn_complete_rate set service_num = {serviceNum} ,service_device_num = {serviceDeviceNum},service_cumulate_num = {serviceAllNum} 
                , update_time = now()  where
                    ref_date = '{toMonthDay}' 
            """
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

#如 2022年12月13号 获取 221213 作为分表后缀
def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 31
        jobName = '核心指标-睡觉提醒-推送成功率'
        tableName = 'analysis_sjtx_complete_rate'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    d = 1
    date = getdate(d)
    yyMMddDate = getdateyyMMdd(d)
    monthdate = getMonthdate(d)
    print (f"{date}日期,执行<数据分析-助眠功能-服务完成情况>报表")
    insertCompleteRate(date)
    insertCompleteRateMonth(monthdate)
